package com.sl.au.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.sl.au.entity.BussinessLevel;
import com.sl.au.entity.Hierarchy;

@Transactional(readOnly = true)
public interface HierarchyRepository extends JpaRepository<Hierarchy, String> {
	@Query(" select a from Hierarchy a where a.groupParent is not null order by a.groupLevel.extended1 asc,a.groupOrder asc")
	List<Hierarchy> findAllNodes();

	@Query(" select a from Hierarchy a where a.groupParent is null and ( a.groupName like %?1% or a.groupIdentify like %?1% or a.groupLevel.title like %?1%) ")
	Page<Hierarchy> getHierarchyByKey(String key, Pageable pageable);

	@Query(" select count(*) from Hierarchy a where a.groupParent is null and ( a.groupName like %?1% or a.groupIdentify like %?1% or a.groupLevel.title like %?1%) ")
	int getHierarchyByKeyCount(String key);

	@Query(" select a from Hierarchy a where a.groupParent is null ")
	Page<Hierarchy> getHierarchy(Pageable pageable);

	@Query(" select count(*) from Hierarchy a where a.groupParent is null ")
	int getHierarchyCount();

	@Modifying
	@Query("update Hierarchy a set a.groupParent = null where a.id=?1")
	int setHierarchy(String id);

	@Query(" select IFNULL(max(a.groupOrder),-2) + 2 from Hierarchy a where a.groupParent = ?1 ")
	int getHierarchyOrder(String groupParent);

	@Query(" select a from Hierarchy a where a.groupParent = ?1 order by a.groupOrder asc")
	List<Hierarchy> getHierarchysByGroupParent(String groupParent);

	Hierarchy findTopByGroupParentAndGroupIdentify(String groupParent, String groupIdentify);

	Page<Hierarchy> findByGroupParent(String groupParent, Pageable pageable);

	@Query(" select a from Hierarchy a where a.groupParent = ?1 and ( a.groupName like %?2% or a.groupIdentify like %?2% or a.groupLevel.title like %?2%) ")
	Page<Hierarchy> findByGroupParentAndKey(String id, String key, Pageable pageable);

	@Query("select a from Hierarchy a where a.groupName = ?1")
	List<Hierarchy> findByGroupName(String groupName);

	@Query("select a from Hierarchy a where a.groupParent = ?1")
	List<Hierarchy> findByParentId(String parentId);
}
